﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using KVB.Common;
using System.Data;

namespace Customer_Profile.eCardReport.DBDao
{
    public class ReportDao
    {
        public static DataSet GetMaturityReport(string sWhere)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@where ", SqlDbType.NVarChar, 4000) };
                parameters[0].Value = sWhere;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Report_Maturity", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.GetMaturityReport", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet DairySumReport(string sLongSQL)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@strSQL ", SqlDbType.NText) };
                parameters[0].Value = sLongSQL;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_execLongSQL", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.DairySumReport", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet ValuebyProdReprot(string start_date,string end_date)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@start_date ", SqlDbType.VarChar,50) ,
                                                new SqlParameter("@end_date ", SqlDbType.VarChar,50) 
                                            };
                parameters[0].Value = start_date;
                parameters[1].Value = end_date;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Report_ValueByProd", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.ValuebyProdReprot", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet Settle_Detail_Report(string start_date,string end_date)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@start_date ", SqlDbType.VarChar,10) ,
                                                new SqlParameter("@end_date ", SqlDbType.VarChar,10) 
                                            };
                parameters[0].Value = start_date;
                parameters[1].Value = end_date;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Report_SettleDetail", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.Settle_Detail_Report", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet NetBankDeal(string start_date, string end_date, string bank_name)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@start_date ", SqlDbType.VarChar,50),
                                                new SqlParameter("@end_date ", SqlDbType.VarChar,50),
                                                new SqlParameter("@bank_name ", SqlDbType.VarChar,250)
                                            };
                parameters[0].Value = start_date;
                parameters[1].Value = end_date;
                parameters[2].Value = bank_name;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Report_NetbankDeal", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.NetBankDeal", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet NetBankSummary(string start_date,string end_date)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@start_date ", SqlDbType.VarChar,50),
                                                new SqlParameter("@end_date ", SqlDbType.VarChar,50),
                                            };
                parameters[0].Value = start_date;
                parameters[1].Value = end_date;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Report_NetbankSum", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.NetBankSummary", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getAllRefDate(string startDate,string endDate)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {
                                                new SqlParameter("@startDate", SqlDbType.VarChar,50),
                                                new SqlParameter("@endDate", SqlDbType.VarChar,50)
                                            };
                parameters[0].Value = startDate;
                parameters[1].Value = endDate;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Base_getRefDate", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.getAllRefDate", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getPressureRefDate(string strStartDate,string strEndDate)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                //SqlParameter[] parameters = {
                //                                new SqlParameter("@startDate", SqlDbType.VarChar,50),
                //                                new SqlParameter("@endDate", SqlDbType.VarChar,50)
                //                            };
                //parameters[0].Value = startDate;
                //parameters[1].Value = endDate;
                string strSQL = "select id,convert(DATE,ref_start_date,120)  AS ref_start_date,convert(DATE,ref_end_date,120)  AS ref_end_date,ref_remark_date from ref_sumDate where ref_start_date between '" + strStartDate + "' and '" + strEndDate + "'";
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.getPressureRefDate", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet GetRemitReport(string sWhere)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@where ", SqlDbType.NVarChar, 4000) };
                parameters[0].Value = sWhere;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Report_Remit", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.GetMaturityReport", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet GetRankingByEntityReport(string sWhere)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@where ", SqlDbType.NVarChar, 4000) };
                parameters[0].Value = sWhere;
                //KVB.Common.Log.WriteDefaultLog("AAAAA:",sWhere);
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "[eCard_Report_RankingByEntity]", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.GetRankingByEntityReport", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet ReAgainRatio()
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "[eCard_Report_ReAgainRatio]");
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.ReAgainRatio", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet ReFundRatio()
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "[eCard_Report_ReFundRatio]");
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.ReAgainRatio", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet GetPressureReport(string strStartDate,string strEndDate)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@start_date ", SqlDbType.VarChar, 50),
                                             new SqlParameter("@end_date ", SqlDbType.VarChar, 50)
                                            };
                parameters[0].Value = strStartDate;
                parameters[1].Value = strEndDate;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "[eCard_Report_Pressure]", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.GetPressureReport", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet GetRankingByDealerReport(string sWhere)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@where ", SqlDbType.NVarChar, 4000) };
                parameters[0].Value = sWhere;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "[eCard_Report_RankingByDealer]", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.GetRankingByDealerReport", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getAgainedAllData(string prod_code)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@prod_code ", SqlDbType.VarChar, 50) };
                parameters[0].Value = prod_code;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "[eCard_Report_Agained]", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.GetRankingByDealerReport", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getCpartyData(string strName)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@Cparty_Name ", SqlDbType.NVarChar, 50) };
                parameters[0].Value = strName;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "[eCard_Report_CpartyDeals]",parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("ReportDao.getCpartyData", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }
    }
}